import pymysql
import requests
from bs4 import BeautifulSoup

def getHTMLText(url, code = 'utf-8'):
    try:
        r = requests.get(url, timeout=15)
        r.raise_for_status()
        r.encoding = code
        return r.text
    except:
        return ""


def getPlayerInfo(url, ifl):
    html = getHTMLText(url)
    soup = BeautifulSoup(html, 'html.parser')
    trs = soup.find(id="per_game_stats").find('tbody').find_all('tr')
    for tr in trs:
        info = []
        for i, td in enumerate(tr.find_all('td')):
            if i not in [0,2,3,7,8,10,11,13,14,17,18]:
                info.append(td.string)
        if info:
            ifl.append(info)


def commit(db):
    try:
        db.commit()
    except:
        db.rollback()


def create_table(db):
    cursor = db.cursor()
    cursor.execute("DROP TABLE IF EXISTS per_game")
    sql = '''CREATE TABLE IF NOT EXISTS per_game (
             pos VARCHAR(10) NOT NULL,g INT,gs INT,mp FLOAT,
             fg FLOAT,fg3 FLOAT,fg2 FLOAT,efg FLOAT,ft FLOAT,orb FLOAT,
             drb FLOAT,trb FLOAT,ast FLOAT,stl FLOAT,blk FLOAT,
             tov FLOAT,pf FLOAT,pts FLOAT)'''
    cursor.execute(sql)
    commit(db)


def insert(db, data):
    cursor = db.cursor()
    for tr in data:
        for i, td in enumerate(tr):
            if not td:
                tr[i] = "null"
        sql = '''INSERT INTO per_game(pos,g,gs,mp,fg,fg3,fg2,efg,
            ft,orb,drb,trb,ast,stl,blk,tov,pf,pts) VALUE('{0[0]}',{0[1]},{0[2]},
            {0[3]},{0[4]},{0[5]},{0[6]},{0[7]},{0[8]},{0[9]},{0[10]},{0[11]},
            {0[12]},{0[13]},{0[14]},{0[15]},{0[16]},{0[17]})'''.format(tr)
        cursor.execute(sql)
    commit(db)


def savePlayerInfo(data):
    db = pymysql.connect('localhost', 'admin1', 'admin1', 'player')
    create_table(db)
    insert(db, data)
    db.close()


def main():
    ifl = []
    for i in range(10):
        url = "https://www.basketball-reference.com/leagues/NBA_"+str(2007+i)+"_per_game.html"
        getPlayerInfo(url, ifl)
    savePlayerInfo(ifl)

main()